from pymysql import Connect
import numpy
import matplotlib.pyplot as plt
# 图一
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
conn = Connect(host='localhost', port=3306, user='root', password='123456', database='sakila_dwh', charset='utf8')
cur = conn.cursor()
sql = """ SELECT dim_store.store_name,COUNT(dim_store.store_key) as '销售笔数'
FROM dim_store JOIN fact_rental ON (dim_store.store_key=fact_rental.store_key)
GROUP BY dim_store.store_key,dim_store.store_name"""
cur.execute(sql)
data = cur.fetchall()
x = []
y = []
for i in range(0, 2):
    x.append(data[i][0])
    y.append(data[i][1])
x_data = [int(data[0][1]), int(data[1][1])]
y_data = [data[0][0], data[1][0]]
plt.bar(x=y_data, height=x_data, color=['r', 'g', 'b', 'c', 'm', 'y'])
for a, b, i in zip(x, y, range(len(x))):
    plt.text(a, b + 0.01, "%.2f" % y[i], ha='center', fontsize=10)
plt.title("The volume of business in each store")
plt.show()
# 图二
sql = """SELECT dim_staff.staff_first_name,SUM(fact_rental.rental_amount) as '销售总额' 
FROM dim_staff JOIN fact_rental on (dim_staff.staff_key=fact_rental.staff_key) 
GROUP BY dim_staff.staff_first_name"""
cur.execute(sql)
data = cur.fetchall()
print(data)
a = []
for i in data:
    a.append(i[1])
name = ["Mike", "Jon", "Jack", "Rose", "Tom"]
dds = plt.figure("kkk")
plt.title("Total sales per employee")
ss = dds.add_subplot(122)
ss.bar(x=name, height=a, color=['r', 'g', 'b', 'c', 'm', 'y'])
for i, j in enumerate(a):
    plt.text(x=i, y=j + 0.2, s=round(j, 2), ha='center')
sd = dds.add_subplot(121)
def make(y):
    def my(pct):
        total = sum(y)
        val = int(round(
            pct * total / 100.0))
        return '{p:.2f}% ({v:d})'.format(p=pct, v=val)
    return my
sd.pie(x=a, labels=name, autopct=make(a))
plt.show()
# 图三
sql = """SELECT dim_film.film_language,sum(rental_amount)  as '销售总额'
FROM fact_rental join dim_film ON (fact_rental.film_key=dim_film.film_key)
GROUP BY dim_film.film_language"""
cur.execute(sql)
data = cur.fetchall()
print(data)
a = []
name = []
for i in data:
    a.append(i[1])
    name.append(i[0])
dds = plt.figure("kkk")
plt.title("Total revenue generated by films in each language")
ss = dds.add_subplot(122)
for i, j in enumerate(a):
    ss.text(x=i, y=j + 0.2, s=round(j, 2), ha='center')
ss.bar(x=name, height=a, color=['r', 'g', 'b', 'c', 'm', 'y'])
sd = dds.add_subplot(121)
def make(y):
    def my(pct):
        total = sum(y)
        val = int(round(
            pct * total / 100.0
        ))
        return '{p:.2f}% ({v:d})'.format(p=pct, v=val)
    return my
sd.pie(x=a, labels=name, autopct=make(a))
plt.show()

# 图四
sql = """SELECT dim_customer.customer_country,SUM(rental_amount) as xfze 
FROM dim_customer JOIN fact_rental on (dim_customer.customer_key=fact_rental.customer_key) 
GROUP BY dim_customer.customer_country
ORDER BY xfze DESC"""
cur.execute(sql)
data = cur.fetchall()
x = []
y = []
for i in range(0, 10):
    x.append(data[i][0])
    y.append(data[i][1])
for i, j in enumerate(y):
    plt.text(x=i, y=j + 0.2, s=round(j, 2), ha='center')
plt.bar(x, y, color=['r', 'b', 'c', 'g', 'y', 'm'])
plt.title('The total amount spent by customers in each country')
plt.show()

# 图五
sql = """SELECT dim_customer.customer_first_name,SUM(rental_amount) as xfze 
FROM dim_customer JOIN fact_rental on (dim_customer.customer_key=fact_rental.customer_key) 
GROUP BY dim_customer.customer_first_name 
ORDER BY xfze DESC"""
cur.execute(sql)
data = cur.fetchall()
print(data)
a = []
name = []
j = 0
for i in data:
    j = j + 1
    if j > 10:
        break
    a.append(i[1])
    name.append(i[0])
for i, j in enumerate(a):
    plt.text(x=i, y=j + 0.2, s=round(j, 2), ha='center')
plt.title(" Total spending of top 10 revenue-generating clients (bar chart)")
plt.bar(x=name, height=a, color=['r', 'g', 'b', 'c', 'm', 'y'])
plt.show()
